package com.buaa.service.impl;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.buaa.dao.ResultDao;
import com.buaa.service.ResultService;
import com.buaa.utils.JacksonUtil;

@Service
public class ResultServiceImpl implements ResultService {

	@Autowired
	private ResultDao resultDao;

	@Override
	public String sumInfo(String loctype) {
		String sql = null;
		switch (loctype) {
		case "province":
			sql = "SELECT '黑龙江省',COUNT(*),128.04741,47.35659 FROM result";
			break;
		case "city":
			sql = "SELECT city,COUNT(*),city.lng,city.lat FROM result ins,city WHERE ins.city = city.city_name GROUP BY city,city.lng,city.lat";
			break;
		case "xian":
			sql = "SELECT county,COUNT(*),county.lng,county.lat FROM result ins,county WHERE ins.county = county.county_name GROUP BY county,county.lng,county.lat";
			break;
		case "danwei":
			sql = "SELECT ins.unit_name,COUNT(*),ins2.lng,ins2.lat FROM result ins,institution ins2 WHERE ins.unit_code = ins2.code GROUP BY ins.unit_name,ins2.lng,ins2.lat";
			break;
		}
		List<Object[]> list = resultDao.common2(sql);
		String res = "";
		Object[] objects = null;
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				objects = list.get(i);
				if (i == list.size() - 1) {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype;
				} else {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype + "|";
				}
			}
		}
		return res;
	}

	@Override
	public Map<String, Long> getCount(String quyu, String loctype, String zhibiao) {
		String sql = "select count(*),result." + zhibiao + " from result ";
		if (loctype.equals("city"))
			sql += " where city='" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " where county='" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += ",institution where result.unit_code = institution.code and institution.name='" + quyu + "'";
		sql += " group by result." + zhibiao + " order by count(*) desc ";
		// System.out.println(sql);

		Map<String, Long> metaData = new LinkedHashMap<String, Long>();
		List<Object[]> list = resultDao.common2(sql);
		if (list == null)
			return metaData;
		for (Object[] objects : list) {
			metaData.put(objects[1].toString(), Math.round(Double.parseDouble(objects[0].toString())));
		}
		return metaData;
	}
	
	@Override
	public String getCitySum() {
		String sql = "SELECT ins2.city,count(*) FROM result ins1,institution ins2 WHERE ins1.unit_code = ins2.code GROUP BY ins2.city";
		List<Object[]> list = resultDao.common2(sql);
		Set<Map<String, Object>> set = new TreeSet<Map<String, Object>>(new Comparator<Map<String, Object>>() {
			@Override
			public int compare(Map<String, Object> o1, Map<String, Object> o2) {
				try {
					int i = Integer.valueOf(o1.get("value").toString())
							.compareTo(Integer.valueOf(o2.get("value").toString()));
					return i > 0 ? 1 : i < 0 ? -1 : 1;
				} catch (Exception e) {
					e.printStackTrace();
					return 1;
				}
			}
		});
		Map<String, Object> map;
		Set<String> citySet = new HashSet<String>();
		citySet.add("哈尔滨市");
		citySet.add("牡丹江市");
		citySet.add("鸡西市");
		citySet.add("佳木斯市");
		citySet.add("齐齐哈尔市");
		citySet.add("大庆市");
		citySet.add("鹤岗市");
		citySet.add("绥化市");
		citySet.add("黑河市");
		citySet.add("七台河市");
		citySet.add("伊春市");
		citySet.add("大兴安岭地区");
		citySet.add("双鸭山市");
		for (Object[] objects : list) {
			String name = objects[0].toString();
			// System.out.println(name);
			// 数据表中有填"其他市"，有没有填的
			if (name.equals("其他市") || name.equals(""))
				continue;
			map = new HashMap<String, Object>();
			Integer value = 0;
			try {
				value = Integer.valueOf(objects[1].toString());
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (citySet.contains(name)) {
				map.put("name", name);
				map.put("value", value);
				set.add(map);
			}
		}
		return JacksonUtil.toJSon(set);
	}

	private String processSql(String sql, String cgmc, String ssdw, String cgmccxfs, String cglx) {
		if (!cgmc.equals(""))
			if (cgmccxfs.equals("1"))
				sql += " and ins1.name LIKE '" + cgmc + "'";
			else
				sql += " and ins1.name LIKE '%" + cgmc + "%'";
		if (!ssdw.equals(""))
			sql += " and ins2.name like '" + ssdw + "'";
		if (!cglx.equals(""))
			sql += " and ins1.type like '" + cglx + "'";
		return sql;
	}

	@Override
	public String contentSearch(String loctype, String cgmc, String ssdw, String cgmccxfs, String cglx) {
		String res = "";
		String sql = null;
		switch (loctype) {
		case "province":
			sql = "SELECT COUNT(*),'黑龙江省',127.91077,48.734684 FROM result ins1,institution ins2 WHERE ins1.unit_code = ins2.code  ";
			sql = processSql(sql, cgmc, ssdw, cgmccxfs, cglx);
			break;
		case "city":
			sql = "SELECT COUNT(*),ins2.city,ins2.lng,ins2.lat FROM result ins1,institution ins2 WHERE ins1.unit_code = ins2.code ";
			sql = processSql(sql, cgmc, ssdw, cgmccxfs, cglx);
			sql += " GROUP BY ins2.city,ins2.lng,ins2.lat";
			break;
		case "xian":
			sql = "SELECT COUNT(*),ins2.county,ins2.lng,ins2.lat FROM result ins1,institution ins2 WHERE ins1.unit_code = ins2.code ";
			sql = processSql(sql, cgmc, ssdw, cgmccxfs, cglx);
			sql += " GROUP BY ins2.county,ins2.lng,ins2.lat";

			break;
		case "danwei":
			sql = "SELECT COUNT(*),ins2.name,ins2.lng,ins2.lat FROM result ins1,institution ins2 WHERE ins1.unit_code = ins2.code ";
			sql = processSql(sql, cgmc, ssdw, cgmccxfs, cglx);
			sql += " GROUP BY ins2.name,ins2.lng,ins2.lat";
			break;
		}

		List<Object[]> list = resultDao.common2(sql);
		if (list == null)
			return res;
		Object[] objects = null;
		for (int i = 0; i < list.size(); i++) {
			objects = list.get(i);
			if (i != list.size() - 1) {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype + "|";
			} else {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype;
			}
		}
		return res;
	}

	@Override
	public List<Object[]> getAllList(String loctype) {
		String sql = null;
		if (loctype.equals("city")) {
			sql = "select ins2.city,count(*) from result ins1,institution ins2 where ins1.unit_code = ins2.code group by ins2.city order by count(*) desc ";
		} else if (loctype.equals("danwei")) {
			sql = "select ins2.name,count(*) from result ins1,institution ins2 where unit_code=code  group by ins2.name order by count(*) desc ";
		}
		return resultDao.common2(sql);
	}

	@Override
	public List<Object> getCountList(String quyu, String loctype, String zhibiao) {
		String sql = "select count(*),ins1." + zhibiao
				+ " from result ins1,institution ins2 where ins1.unit_code = ins2.code ";
		if (loctype.equals("city"))
			sql += " and ins2.city='" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " and ins2.county='" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += " and ins2.name='" + quyu + "'";
		sql += " group by ins1." + zhibiao + " order by count(*) desc ";
		List<Object[]> countlist = resultDao.common2(sql);
		String name;
		if (zhibiao.equals("type")) {
			name = "形式";
		} else {
			name = "待定";
		}
		List<Object> ret = new ArrayList<>();
		ret.add(name);
		ret.add(countlist);
		return ret;
	}

	@Override
	public List<Object[]> getCxTable1Info(String cgmc, String ssdw, String cgmccxfs, String cglx, String quyu,
			String loctype) {
		String sql = "select ins2.name ins2name,ins1.name,ins2.id,ins2.unit_code from institution ins1,result ins2 where ins2.unit_code = ins1.code ";
		if (loctype.equals("city"))
			sql += " and ins1.city = '" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " and ins1.county  = '" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += " and ins1.name= '" + quyu + "'";
		if (!cgmc.equals(""))
			if (cgmccxfs.equals("1"))
				sql += " and ins2.name like '" + cgmc + "'";
			else
				sql += " and ins2.name like '%" + cgmc + "%'";
		if (!ssdw.equals(""))
			sql += " and ins1.name like '" + ssdw + "'";
		if (!cglx.equals(""))
			sql += " and ins2.type  like '" + cglx + "'";
		return resultDao.common2(sql);
	}

	@Override
	public List<Object> getTable1Info(String zhibiao, String zhibiaomc, String quyu, String loctype, String page) {
		String sql = "";
		String sqlnum = "";
		if (loctype.equals("province")) {
			sql = "select ins2.name ins2name,ins1.name,id,code from result ins1,institution ins2 where unit_code = code ";
			sqlnum = "select count(*) from result ins1,institution ins2 where unit_code = code ";
		} else if (loctype.equals("city")) {
			sqlnum = "select count(*) from result ins1,institution ins2 where unit_code = code and ins2.city ='" + quyu
					+ "'";
			sql = "select ins2.name ins2name,ins1.name,id,code from result ins1,institution ins2 where unit_code = code and ins2.city ='"
					+ quyu + "'";
		} else if (loctype.equals("xian")) {
			sqlnum = "select count(*) from result ins1,institution ins2 where unit_code = code and ins2.county  ='"
					+ quyu + "'";
			sql = "select ins2.name ins2name,ins1.name,id,code from result ins1,institution ins2 where unit_code = code and ins2.county  ='"
					+ quyu + "'";
		} else if (loctype.equals("danwei")) {
			sqlnum = "select count(*) from result ins1,institution ins2 where unit_code = code and ins2.name  ='" + quyu
					+ "'";
			sql = "select ins2.name ins2name,ins1.name,id,code from result ins1,institution ins2 where unit_code = code and ins2.name  ='"
					+ quyu + "'";
		}
		if (!zhibiao.equals("")) {
			sqlnum += " and ins1." + zhibiao + "='" + zhibiaomc + "'";
			sql += " and ins1." + zhibiao + "='" + zhibiaomc + "'";
		}

		int curpage = 1;
		if (page != null)
			curpage = Integer.valueOf(page);
		// 总页数
		int totalpage = 0;
		// 下一页
		int nextpage = 0;
		// 上一页
		int lastpage = 0;
		// 每页显示条数
		int pagenum = 10;
		// 数据总条数
		int total = 0;
		List<Object> rsnum = resultDao.common1(sqlnum);
		if (rsnum != null) {
			if (Integer.valueOf(rsnum.get(0).toString()) % pagenum != 0)
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum + 1;
			else
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum;
			total = Integer.valueOf(rsnum.get(0).toString());
		}
		if (curpage == 1)
			lastpage = 1;
		else
			lastpage = curpage - 1;
		if (curpage < totalpage)
			nextpage = curpage + 1;
		else {
			nextpage = totalpage;
			pagenum = total - pagenum * (totalpage - 1);
		}
		// 显示区间
		int beginnum = (curpage - 1) * 10;
		int endnum = beginnum + pagenum;

		List<Object[]> rs = resultDao.common2(sql);
		List<Object> ret = new ArrayList<>();
		ret.add(totalpage);
		ret.add(total);
		ret.add(curpage);
		ret.add(nextpage);
		ret.add(lastpage);
		ret.add(pagenum);
		ret.add(beginnum);
		ret.add(endnum);
		ret.add(rs);
		return ret;
	}
	
	@Override
	public List<Object[]> getTable2Info(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("ins1.name,");
		sb.append("ins1.type,");
		sb.append("ins2.city,");
		sb.append("ins2.county,");
		sb.append("ins2.name ins2name,");
		sb.append("code");
		String sql = "select " + sb.toString() + " from result ins1,institution ins2 where ins2.code=ins1.unit_code and id='" + id + "'";
		return resultDao.common2(sql);
	}

	@Override
	public List<Object> findResultType() {
		return resultDao.findResultType();
	}

}
